/**
 *  Copyright (C) 2008-2013  Telosys project org. ( http://www.telosys.org/ )
 *
 *  Licensed under the GNU LESSER GENERAL PUBLIC LICENSE, Version 3.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *          http://www.gnu.org/licenses/lgpl.html
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 */
package org.telosys.persistence.sql;

import java.io.Serializable;

import org.telosys.common.TelosysRuntimeException;
import org.telosys.persistence.TelosysPersistenceException;
import org.telosys.persistence.impl.QueryAncestor;
import org.telosys.persistence.syntax.SqlSyntax;

/**
 * The "definition" for a SQLDataSet <br>
 * It contains the SQL request that will be used to load the SQLDataSet <br>
 * 
 * @author Laurent GUERIN
 *  
 */
public class SqlQuery  extends QueryAncestor implements Serializable
{
	private static final long serialVersionUID = 1L;
	
//    private final static int[] VOID_PARAM_TYPES = new int[0];
    
    private String  _sFullSqlSelectRequest = null ;
    
//    private boolean _bStaticDefinition     = false ;    
    
    private String _sSqlSelect        = null;

    private String _sSqlFrom          = null;

    private String _sSqlWhere         = null;

    private String _sOriginalSqlWhere = null;

    private String _sSqlOption        = "";

//    private int[]  _parametersTypes   = null;

    private int    _iMaxRows          = 0 ; // No max row by default

    //private int    _iLinesPerPage     = 20;  // 20 lines per page (by default)

    //private int    _iDatabaseId       = 0;

    //------------------------------------------------------------------------------------------
//    /**
//     *  
//     */
//    private void init(String sSqlSelect, String sSqlFrom, String sSqlWhere, String sSqlOption, int[] paramTypes)
//    {
//        //_iDatabaseId = ConnectionManager.getDefaultDatabase(); // v 0.9.8 ( 29/08/2007 )
//        
//        _sSqlSelect = (sSqlSelect != null ? sSqlSelect : "ERR_no_select");
//        _sSqlFrom = (sSqlFrom != null ? sSqlFrom : "ERR_no_from");
//        _sSqlWhere = (sSqlWhere != null ? sSqlWhere : "");
//        _sOriginalSqlWhere = _sSqlWhere;
//        _sSqlOption = (sSqlOption != null ? sSqlOption : "");
////        if (paramTypes != null)
////        {
////            //--- Clone to preserve "private"
////            _parametersTypes = (int[]) paramTypes.clone();
////        }
////        else
////        {
////            _parametersTypes = VOID_PARAM_TYPES ; // new int[0];
////        }
//    }
    
//    //------------------------------------------------------------------------------------------
//    private void initParamTypes(int[] paramTypes)
//    {
//        //_iDatabaseId = ConnectionManager.getDefaultDatabase(); 
//        
//        //_sFullSqlSelectRequest = sSqlSelectRequest ;
//        if (paramTypes != null)
//        {
//            //--- Clone to preserve "private"
//            _parametersTypes = (int[]) paramTypes.clone();
//        }
//        else
//        {
//            _parametersTypes = VOID_PARAM_TYPES ; // new int[0];
//        }
//    }

    //------------------------------------------------------------------------------------------
    // CONSTRUCTORS
    //------------------------------------------------------------------------------------------
    /**
     * Constructor for a full SQL request in a single string.
     * NB: With this constructor, the WHERE clause cannot be dynamic ( alterWhere cannot be used ) 
     * @param sSqlSelectRequest
     */
    public SqlQuery(String sSqlSelectRequest)
    {
    	super( SqlSyntax.countParameters(sSqlSelectRequest) );

        if ( sSqlSelectRequest == null )
        {
            throw new TelosysRuntimeException ( "SQLDataSetDefinition constructor : SQL request is null" );
        }
        
        _sFullSqlSelectRequest = sSqlSelectRequest ;

//        _bStaticDefinition = true ;
//        initParamTypes(null);
        
        //----- NEW : SPLIT THE STATIC REQUEST IN 4 PARTS 
		SqlQuerySplitter splitter = new SqlQuerySplitter(_sFullSqlSelectRequest);
		
		_sSqlSelect = splitter.getSqlSelect() ;
		_sSqlFrom   = splitter.getSqlFrom() ;
		_sSqlWhere  = splitter.getSqlWhere() ;
		_sSqlOption = splitter.getSqlOrderByGroupBy();
        
        checkSqlParts() ;
    }
    
    //------------------------------------------------------------------------------------------
//    /**
//     * Constructor for full SQL request in a single string, with parameters.
//     * NB: With this constructor, the WHERE clause cannot be dynamic ( alterWhere cannot be used ) 
//     * @param sSqlSelectRequest
//     * @param paramTypes
//     */
//    public DataSetSqlQuery(String sSqlSelectRequest, int[] paramTypes)
//    {
//        if ( sSqlSelectRequest == null )
//        {
//            throw new TelosysRuntimeException ( "SQLDataSetDefinition constructor : SQL request is null" );
//        }
//        _bStaticDefinition = true ;
//        init(sSqlSelectRequest, paramTypes);
//    }
    
    //------------------------------------------------------------------------------------------
    /**
     * Constructor.
     * The WHERE clause is potentially dynamic ( alterWhere can be used ) 
     * @param sSqlSelect - the SELECT part of the request without parameters ( ie SELECT A, B, C )
     * @param sSqlFrom - the FROM part of the request without parameters ( ie FROM T1, T2 )
     * @param sSqlWhere - the WHERE part of the request with parameters ( ie WHERE A > ? AND B = ? )
     * @param sSqlOption - the last part of the request without parameters ( ie ORDER BY A, B, C )
     */
    public SqlQuery(String sSqlSelect, String sSqlFrom, String sSqlWhere, String sSqlOption)
    {
    	super( SqlSyntax.countParameters(sSqlWhere) );
    	
//        _sSqlSelect = (sSqlSelect != null ? sSqlSelect : "ERR_no_select");
//        _sSqlFrom = (sSqlFrom != null ? sSqlFrom : "ERR_no_from");
//        _sSqlWhere = (sSqlWhere != null ? sSqlWhere : "");
//        _sOriginalSqlWhere = _sSqlWhere;
//        _sSqlOption = (sSqlOption != null ? sSqlOption : "");
    	
        //init(sSqlSelect, sSqlFrom, sSqlWhere, sSqlOption, null);
//        initParamTypes(null);

        _sSqlSelect = sSqlSelect ;
        _sSqlFrom   = sSqlFrom ;
        _sSqlWhere  = sSqlWhere ;
        _sSqlOption = sSqlOption ;
        
        checkSqlParts() ;
    }

    private void checkSqlParts() 
    {
    	if ( null == _sSqlSelect ) {
    		throw new TelosysPersistenceException("Invalid SQL request (no SELECT)");
    	}
    	if ( null == _sSqlFrom ) {
    		throw new TelosysPersistenceException("Invalid SQL request (no FROM)");
    	}
    	if ( null == _sSqlWhere ) {
    		_sSqlWhere = "" ;
    	}
    	if ( null == _sSqlOption ) {
    		_sSqlOption = "" ;
    	}
        _sOriginalSqlWhere = _sSqlWhere;
    }
//    //------------------------------------------------------------------------------------------
//    /**
//     * Constructor.
//     * The WHERE clause is potentially dynamic ( alterWhere can be used ) 
//     * @param sSqlSelect
//     * @param sSqlFrom
//     * @param sSqlWhere
//     * @param sSqlOption
//     * @param paramTypes
//     */
//    public DataSetSqlQuery(String sSqlSelect, String sSqlFrom, String sSqlWhere, String sSqlOption, int[] paramTypes)
//    {
//        init(sSqlSelect, sSqlFrom, sSqlWhere, sSqlOption, paramTypes);
//    }

    //------------------------------------------------------------------------------------------
    public void applyCriteria(Criteria criteria)
    {
    	alterWhere(criteria);
    	Object[] dynamicParameters = getDynamicParameters(criteria);
    	this.resetAllParameters(dynamicParameters);
    }
    
    /**
     * Changes the initial SQL where clause using criteria to build the new WHERE clause dynamicaly
     * NB: if the SQLDataSet definition is static, this method cannot be used ( throws a TelosysRuntimeException )
     * @param criteria
     */
    private void alterWhere(Criteria criteria)
    {
//        if ( _bStaticDefinition )
//        {
//            throw new TelosysRuntimeException ( "alterWhere cannot be used with a static request definition." );            
//        }
        if (_sOriginalSqlWhere.trim().toLowerCase().startsWith("where"))
        {
            //--- Default = original static WHERE
            _sSqlWhere = _sOriginalSqlWhere.trim() ;
            //--- Static WHERE clause => add the dynamic WHERE clause if it exists
            String sDynamic = criteria.getDynamicWhere();
            if ( sDynamic != null )
            {
                if ( sDynamic.trim().length() > 0 )
                {
                    _sSqlWhere = _sOriginalSqlWhere.trim() + " and " + sDynamic ;                 
                }
            }
        }
        else
        {
            //--- No static WHERE clause => rebuild all the WHERE clause
            String sDynamic = criteria.getDynamicWhere();
            if ( sDynamic != null )
            {
                if ( sDynamic.trim().length() > 0 )
                {
                    _sSqlWhere = " where " + sDynamic ;
                }
            }
        }
        //--- Set the new parameters types
//        _parametersTypes = criteria.getDynamicTypes();
        //trace("alterWhere() : " + _sSqlWhere);

        
        //_criterias = criterias ;
    }

    private Object[] getDynamicParameters(Criteria criteria)  // TODO : move in Criteria
    {
    	//System.out.println("getDynamicParameters :");
        //--- Determine the array size
    	Criterion[] usedCriteria = criteria.getUsefulCriteria();
        //--- Build the String array
        Object[] params = new Object[usedCriteria.length];
        int x = 0;
        for ( Criterion criterion : usedCriteria )
        {
        	//System.out.println(" . Criterion " + x + " : " + criterion.getParamValueAsString() );
        	params[x++] = criterion.getParamValueAsObject();
        }
        return params;
    }
    
    //------------------------------------------------------------------------------------------
    // SETTERS & GETTERS
    //------------------------------------------------------------------------------------------
    //--- Setters
    /**
     * Set the maximum number of rows <br>
     * 
     * @param maximum - the maximum number of rows ( or 0 for no limit ) 
     */
    public void setMaxRows(int maximum)
    {
        _iMaxRows = maximum;
    }

//    //-----------------------------------------------------------------------------
//    /**
//     * Set the number of lines per page (cannot exceeded the maximum number of rows) 
//     * @param v
//     */
//    public void setLinesPerPage(int v)
//    {
//        if (v <= _iMaxRows)
//        {
//            _iLinesPerPage = v;
//        }
//    }

//    //-----------------------------------------------------------------------------
//    /**
//     * Set the id of the database to use
//     * @param v
//     */
//    public void setDatabaseId(int v)
//    {
//        _iDatabaseId = v;
//    }

    //--- Getters

    //-----------------------------------------------------------------------------
    /**
     * @return the "SELECT part" of the SQL request
     */
    public String getSqlSelect()
    {
        return _sSqlSelect;
    }

    //-----------------------------------------------------------------------------
    /**
     * @return the "WHERE part" of the SQL request
     */
    public String getSqlWhere()
    {
        return _sSqlWhere;
    }

    //-----------------------------------------------------------------------------
    /**
     * @return the "FROM part" of the SQL request
     */
    public String getSqlFrom()
    {
        return _sSqlFrom;
    }

    //-----------------------------------------------------------------------------
    /** 
     * @return the "OPTIONAL part" of the SQL request
     */
    public String getSqlOption()
    {
        return _sSqlOption;
    }

//    //-----------------------------------------------------------------------------
//    /**
//     * Return the number of parameters expected to execute the SQL request
//     * 
//     * @return : number of parameters expected
//     */
//    public int getNbParams()
//    {
//        return _parametersTypes.length;
//    }

//    //-----------------------------------------------------------------------------
//    /**
//     * @return number of lines per page
//     */
//    public int getLinesPerPage()
//    {
//        return _iLinesPerPage;
//    }

    //-----------------------------------------------------------------------------
    /** 
     * @return maximum number of rows
     */
    public int getMaxRows()
    {
        return _iMaxRows;
    }

//    //-----------------------------------------------------------------------------
//    /**
//     * @return id of the database to use
//     */
//    public int getDatabaseId()
//    {
//        return _iDatabaseId;
//    }

//    //-----------------------------------------------------------------------------
//    /**
//     * Returns a string containing all the parameters types (to be used as a debug information)
//     * @return
//     */
//    private String getParametersTypes()
//    {
//        StringBuffer sb = new StringBuffer(100);
//        for ( int i = 0 ; i < _parametersTypes.length ; i++ )
//        {
//            if (i > 0)
//            {
//                sb.append("|");
//            }
//            sb.append("" + _parametersTypes[i]);
//        }
//        return sb.toString();
//    }

    //-----------------------------------------------------------------------------
    /**
     * Return the full SQL request for SELECT COUNT(1) FROM ... WHERE ...
     * 
     * @return : SQL request
     */
    public String getFullSqlCount()
    {
//        String sSelectCount = "SELECT COUNT(1) " ;
//        String sSQL = null ;
//        if ( _bStaticDefinition )
//        {
//            String s = _sFullSqlSelectRequest.toUpperCase();
//            //--- Replace "SELECT a, b, c ..." by "SELECT COUNT(1)" 
//            int i = s.indexOf("FROM") ;
//            if ( i > 0 )
//            {
//                sSQL = sSelectCount + _sFullSqlSelectRequest.substring(i);
//                //--- Remove "ORDER BY" if any
//                sSQL = StrUtil.cutBeforeIgnoreCase(sSQL, "ORDER");
//                //--- Remove "GROUP BY" if any
//                sSQL = StrUtil.cutBeforeIgnoreCase(sSQL, "GROUP");
//            }
//            else 
//            {
//                throw new TelosysRuntimeException ( "Invalid SQL request ( 'FROM' expected )" );            
//            }
//        }
//        else
//        {
//	        sSQL = sSelectCount + _sSqlFrom + " " + _sSqlWhere;
//        }
//        //trace("getFullSqlCount() : " + sSQL);
//        if ( sSQL == null )
//        {
//            throw new TelosysRuntimeException ( "Cannot get SQL COUNT request." );            
//        }
//        return sSQL ;
        
        return "SELECT COUNT(1) " + _sSqlFrom + " " + _sSqlWhere ;
    }

    //-----------------------------------------------------------------------------
    /**
     * Return the full SQL request to retrieve data
     * 
     * @return : SQL request
     */
    public String getFullSqlSelect()
    {
//        //if ( _bStaticDefinition )
//        if ( _sFullSqlSelectRequest != null )
//        {
//            return _sFullSqlSelectRequest ;
//        }
//        else
//        {
//            String sSQL = _sSqlSelect + " " + _sSqlFrom + " " + _sSqlWhere + " " + _sSqlOption;
//            //trace("getFullSqlSelect() : " + sSQL);
//            return sSQL;
//        }
        return _sSqlSelect + " " + _sSqlFrom + " " + _sSqlWhere + " " + _sSqlOption;
    }

//    //-----------------------------------------------------------------------------
//    /**
//     * Return the first row of the given page (calculated with "lines per page")
//     * 
//     * @param iPage :
//     *            page number (from 1 to N )
//     * @return : the row number ( from 1 to N )
//     */
//    public int getFirstRowForPage(int iPage)
//    {
//        return ((iPage - 1) * _iLinesPerPage) + 1;
//    }

//    //-----------------------------------------------------------------------------
//    /**
//     * Return the last row of the given page (calculated with "lines per page")
//     * 
//     * @param iPage :
//     *            page number (from 1 to N )
//     * @return : the row number ( from 1 to N )
//     */
//    public int getLastRowForPage(int iPage)
//    {
//        return (iPage * _iLinesPerPage);
//    }

    //-----------------------------------------------------------------------------
//    /**
//     * Casts all the given parameters from String to the appropriate type
//     * @param strParams array of String parameters  
//     * @return array of instances of classes corresponding to the expected parameters types 
//     */
//    private Object[] castParameters(String strParams[]) //throws TelosysException
//    {
//        if (strParams == null)
//        {
//            return new Object[0];
//        }
//        int iParamLength = _parametersTypes.length;
//        if (strParams.length < iParamLength)
//        {
//            throw new TelosysRuntimeException("Invalid parameters length (" + strParams.length + " < " + iParamLength + ")");
//        }
//
//        Object[] objParams = new Object[iParamLength];
//
//        int i = 0;
//        String sParam = "(null)";
//        try
//        {
//            for ( i = 0 ; i < iParamLength ; i++ )
//            {
//                sParam = strParams[i];
//                objParams[i] = Cast.toObject(strParams[i], _parametersTypes[i]);
//            }
//        } catch (Exception e)
//        {
//            throw new TelosysRuntimeException("Cannot cast parameter " + (i + 1) + " : '" + sParam + "' ", e);
//        } catch (Throwable t)
//        {
//            throw new TelosysRuntimeException("Cannot cast parameter " + (i + 1) + " : '" + sParam + "' ", t);
//        }
//
//        return objParams;
//    }

}